# 导入依赖包
import pandas as pd
from matplotlib import pyplot as plt

# 设置全局字体为黑体
plt.rcParams['font.sans-serif'] = ['SimHei']  # 适用于 Windows 系统
plt.rcParams['axes.unicode_minus'] = False  # 修复负号显示问题

# 数据处理
# 读取数据
data = pd.read_parquet("../static/data/user_behavior_20141118_20141218_edit.parquet")
# 查看缺失值
print(data.isnull().sum())

# 分割天(date)和小时(hour) 2014
data['date'] = data['time'].map(lambda x: x.split(' ')[0])
data['hour'] = data['time'].map(lambda x: x.split(' ')[1])
print(data.head(10))

# 查看数据集有多少天的数据
print('数据集时间跨度是：', len(set(data['date'])), '天')
# 查看字段类型
print(data.dtypes)

# 转数据类型
data['user_id'] = data['user_id'].astype(object)
data['item_id'] = data['item_id'].astype(object)
data['item_category'] = data['item_category'].astype(object)
data['date'] = pd.to_datetime(data['date']).dt.strftime('%Y-%m-%d')
data['hour'] = data['hour'].astype('int32')
print(data.dtypes)

# 数据分析 & 可视化

# Part1. 流量分析
# 先从宏观的流量分析入手。主要关注访问量(PV)与独立访问量(UV)
#
# 访问量(PV)：全名为Page View, 基于用户每次对淘宝页面的刷新次数，用户每刷新一次页面或者打开新的页面就记录就算一次访问。
# 独立访问量(UV)：全名为Unique Visitor，一个用户若多次访问淘宝只记录一次，熟悉SQL的小伙伴会知道，本质上是unique操作。

# 1.1 基于天级别访问流量分析

# 计算 PV：
# PV：select count(user_id) from data group by date
pv_grouped_data = data.groupby('date')
pv_daily_count = pv_grouped_data['user_id'].agg('count')
pv_daily_reset = pv_daily_count.reset_index()
pv_daily = pv_daily_reset.rename(columns={'user_id': 'pv_daily'})
# 输出结果的前几行
print(pv_daily.head())

# 计算 UV：
# UV：select count(distinct user_id) from data group by date
# uv_daily = data.groupby('date')['user_id'].agg('unique')
# 输出 2014-11-18  [104221274, 103582986, 103802946, 100684618, 1...
uv_grouped_data = data.groupby('date')
# 对分组后的数据，对 'user_id' 列应用 lambda 函数，计算每个分组中唯一的 user_id 数量
uv_daily_unique = uv_grouped_data['user_id'].apply(lambda x: len(x.unique()))
uv_daily_reset = uv_daily_unique.reset_index()
uv_daily = uv_daily_reset.rename(columns={'user_id': 'uv_daily'})
# 输出结果的前几行
print(uv_daily.head())

# 基于天级的PV和UV数据结果，进行可视化展示
# 横坐标每天的日期date，纵坐标pv_daily和uv_daily
pv_date = pv_daily['date'].tolist()
pv_daily = pv_daily['pv_daily'].tolist()
uv_date = uv_daily['date'].tolist()
uv_daily = uv_daily['uv_daily'].tolist()

from pyecharts.charts import Line
from pyecharts import options as opts

# 创建PV折线图
line_pv = (
    Line(init_opts=opts.InitOpts(width="100vw"))
    .add_xaxis(pv_date)
    .add_yaxis("PV", pv_daily, is_smooth=True, label_opts=opts.LabelOpts(is_show=True, rotate=45),
               markline_opts=opts.MarkLineOpts(
                   data=[
                       opts.MarkLineItem(type_="average", name="平均值", linestyle_opts=opts.LineStyleOpts(
                           type_="dashed"
                       )),
                       opts.MarkLineItem(type_="min", name="最小值", linestyle_opts=opts.LineStyleOpts(
                           color="#FCC524",
                           type_="dashed"
                       )),
                       opts.MarkLineItem(type_="max", name="最大值", linestyle_opts=opts.LineStyleOpts(
                           color="#E63E31",
                           type_="dashed"
                       )),
                   ],
               ))
    .set_global_opts(
        title_opts=opts.TitleOpts(title="pv_day"),
        xaxis_opts=opts.AxisOpts(type_="category", name="date"),
        yaxis_opts=opts.AxisOpts(type_="value", name="pv_count", name_location="middle", name_gap=65),
        legend_opts=opts.LegendOpts(is_show=False),
    )
)

# 创建UV折线图
line_uv = (
    Line(init_opts=opts.InitOpts(width="100vw"))
    .add_xaxis(uv_date)
    .add_yaxis("UV", uv_daily, is_smooth=True, label_opts=opts.LabelOpts(is_show=True, rotate=45),
               markline_opts=opts.MarkLineOpts(
                   data=[
                       opts.MarkLineItem(type_="average", name="平均值", linestyle_opts=opts.LineStyleOpts(
                           type_="dashed"
                       )),
                       opts.MarkLineItem(type_="min", name="最小值", linestyle_opts=opts.LineStyleOpts(
                           color="#FCC524",
                           type_="dashed"
                       )),
                       opts.MarkLineItem(type_="max", name="最大值", linestyle_opts=opts.LineStyleOpts(
                           color="#E63E31",
                           type_="dashed"
                       )),
                   ],
               ))
    .set_global_opts(
        title_opts=opts.TitleOpts(title="uv_day"),
        xaxis_opts=opts.AxisOpts(type_="category", name="date"),
        yaxis_opts=opts.AxisOpts(type_="value", name="uv_count", name_location="middle", name_gap=55),
        legend_opts=opts.LegendOpts(is_show=False),
    )
)
# 渲染折线图
line_pv.render("../static/html/pv_day_line_chart.html")
line_uv.render("../static/html/uv_day_line_chart.html")

'''
import pymysql

# 连接到 MySQL
host = 'localhost'
user = 'root'
password = '123456'
database = 'user_behavior_db_2017'
connection = pymysql.connect(
    host=host,
    user=user,
    password=password,
    database=database
)
cursor = connection.cursor()
'''
'''
# 插入 pv_daily 数据
for date, pv in zip(pv_date, pv_daily):
    sql = """
    INSERT INTO pv_daily (date, pv_count)
    VALUES (%s, %s)
    """
    cursor.execute(sql, (date, pv))
# 插入 uv_daily 数据
for date, uv in zip(uv_date, uv_daily):
    sql = """
    INSERT INTO uv_daily (date, uv_count)
    VALUES (%s, %s)
    """
    cursor.execute(sql, (date, uv))

# 提交事务
connection.commit()

print("数据已成功插入到 MySQL 数据库中！")
'''

# 1.2 基于小时级别访问流量分析
# 计算 PV：
# PV：select count(user_id) from data group by hour
pv_hour = data.groupby('hour')['user_id'].agg('count')
pv_hour = pv_hour.reset_index()
pv_hour = pv_hour.rename(columns={'user_id': 'pv_hour'})
print(pv_hour.head())

# 计算 UV：
# UV：select count(distinct user_id) from data group by hour
uv_hour = data.groupby('hour')['user_id'].apply(lambda x: len(x.unique()))
uv_hour = uv_hour.reset_index()
uv_hour = uv_hour.rename(columns={'user_id': 'uv_hour'})
print(uv_hour.head())

# 基于小时级的PV和UV数据结果，进行可视化展示
hour = pv_hour['hour'].tolist()
pv_hour = pv_hour['pv_hour'].tolist()
uv_hour = uv_hour['uv_hour'].tolist()

print("\n\nuv_hour:\n\n")
print(uv_hour)

from pyecharts.charts import Line
from pyecharts import options as opts

# 创建PV折线图
line_pv_hour = (
    Line(init_opts=opts.InitOpts(width="100vw"))
    .add_xaxis(hour)
    .add_yaxis("PV", pv_hour, is_smooth=True, label_opts=opts.LabelOpts(is_show=True, rotate=45),
               markline_opts=opts.MarkLineOpts(
                   data=[
                       opts.MarkLineItem(type_="average", name="平均值", linestyle_opts=opts.LineStyleOpts(
                           type_="dashed"
                       )),
                       opts.MarkLineItem(type_="min", name="最小值", linestyle_opts=opts.LineStyleOpts(
                           color="#FCC524",
                           type_="dashed"
                       )),
                       opts.MarkLineItem(type_="max", name="最大值", linestyle_opts=opts.LineStyleOpts(
                           color="#E63E31",
                           type_="dashed"
                       )),
                   ],
               ))
    .set_global_opts(
        title_opts=opts.TitleOpts(title="pv_hour"),
        xaxis_opts=opts.AxisOpts(type_="category", name="hour"),
        yaxis_opts=opts.AxisOpts(type_="value", name="pv_count", name_location="middle", name_gap=65),
        legend_opts=opts.LegendOpts(is_show=False),
    )
)

# 创建UV折线图
line_uv_hour = (
    Line(init_opts=opts.InitOpts(width="100vw"))
    .add_xaxis(hour)
    .add_yaxis("UV", uv_hour, is_smooth=True, label_opts=opts.LabelOpts(is_show=True, rotate=45),
               markline_opts=opts.MarkLineOpts(
                   data=[
                       opts.MarkLineItem(type_="average", name="平均值", linestyle_opts=opts.LineStyleOpts(
                           type_="dashed"
                       )),
                       opts.MarkLineItem(type_="min", name="最小值", linestyle_opts=opts.LineStyleOpts(
                           color="#FCC524",
                           type_="dashed"
                       )),
                       opts.MarkLineItem(type_="max", name="最大值", linestyle_opts=opts.LineStyleOpts(
                           color="#E63E31",
                           type_="dashed"
                       )),
                   ],
               )
               )
    .set_global_opts(
        title_opts=opts.TitleOpts(title="uv_hour"),
        xaxis_opts=opts.AxisOpts(type_="category", name="hour"),
        yaxis_opts=opts.AxisOpts(type_="value", name="uv_count", name_location="middle", name_gap=55),
        legend_opts=opts.LegendOpts(is_show=False),
    )

)

# 渲染折线图
line_pv_hour.render("../static/html/pv_hour_line_chart.html")
line_uv_hour.render("../static/html/uv_hour_line_chart.html")
'''
# 插入 pv_hourly 数据
for hour_val, pv in zip(hour, pv_hour):
    sql = """
    INSERT INTO pv_hourly (hour, pv_count)
    VALUES (%s, %s)
    """
    cursor.execute(sql, (hour_val, pv))
# 插入 uv_hourly 数据
for hour_val, uv in zip(hour, uv_hour):
    sql = """
    INSERT INTO uv_hourly (hour, uv_count)
    VALUES (%s, %s)
    """
    cursor.execute(sql, (hour_val, uv))

# 提交事务
connection.commit()

print("数据已成功插入到 MySQL 数据库中！")
'''
# 可以看出，PV的高峰值出现在20点之后，可能的原因是淘宝的主力消费人群是工薪阶层，
# 这部分群体在下班后开始使用淘宝浏览购物；UV的值比较恒定，上午10点之后便没有出现大的波动，
# 一个可能的原因是用户早晨也会刷一下淘宝，比如看看物流状态，UV值在一天之内就不会再有大的变化波动了。
# 另外也可以看出，凌晨2点之后，PV/UV的趋势一致，均是一天中流量最小的时间段。


# 从"日PV/UV"趋势看，双十二当天的总体流量会出现明显的峰值。那么双十二当天基于小时的用户访问数据会有变化吗？
data_1212 = data.loc[data['date'] == '2014-12-12']
print(data_1212.head(10))
# 计算每小时的PV
pv_hour_1212 = data_1212.groupby('hour')['user_id'].count().reset_index().rename(columns={'user_id': 'pv_hour_1212'})
uv_hour_1212 = data_1212.groupby('hour')['user_id'].apply(lambda x: len(x.unique())).reset_index().rename(
    columns={'user_id': 'uv_hour_1212'})
print(pv_hour_1212.head(10))

# 基于双十二小时级的PV和UV数据结果，进行可视化展示
pv_hour_1212 = pv_hour_1212['pv_hour_1212'].tolist()
uv_hour_1212 = uv_hour_1212['uv_hour_1212'].tolist()

print("\n\nuv_hour_1212:\n\n")
print(uv_hour_1212)
# 为了方便对比，和30日总体的小时级别PV/UV变化趋势做对比
# 可视化PV
# fig_pv, axes = plt.subplots(2, 1, sharex=True)
# pv_hour.plot(x='hour', y='pv_hour', ax=axes[0], colormap='cividis')
# pv_hour_1212.plot(x='hour', y='1212_pv_hour', ax=axes[1], colormap='RdGy')
# axes[0].set_title('pv_hour')
# axes[1].set_title('pv_hour_1212')
# plt.show()
#
# # 可视化 UV
# fig_uv, axes = plt.subplots(2, 1, sharex=True)
# uv_hour.plot(x='hour', y='uv_hour', ax=axes[0], colormap='cividis')
# uv_hour_1212.plot(x='hour', y='1212_uv_hour', ax=axes[1], colormap='RdGy')
# axes[0].set_title('uv_hour')
# axes[1].set_title('uv_hour_1212')
# plt.show()

from pyecharts.charts import Line
from pyecharts import options as opts

# 创建PV折线图
line_pv_hour_1212 = (
    Line(init_opts=opts.InitOpts(width="100vw"))
    .add_xaxis(hour)
    .add_yaxis("PV", pv_hour_1212, is_smooth=True, label_opts=opts.LabelOpts(is_show=True, rotate=45),
               markline_opts=opts.MarkLineOpts(
                   data=[
                       opts.MarkLineItem(type_="average", name="平均值", linestyle_opts=opts.LineStyleOpts(
                           type_="dashed"
                       )),
                       opts.MarkLineItem(type_="min", name="最小值", linestyle_opts=opts.LineStyleOpts(
                           color="#FCC524",
                           type_="dashed"
                       )),
                       opts.MarkLineItem(type_="max", name="最大值", linestyle_opts=opts.LineStyleOpts(
                           color="#E63E31",
                           type_="dashed"
                       )),
                   ],
               ))
    .set_global_opts(
        title_opts=opts.TitleOpts(title="pv_hour"),
        xaxis_opts=opts.AxisOpts(type_="category", name="hour"),
        yaxis_opts=opts.AxisOpts(type_="value", name="pv_count", name_location="middle", name_gap=65),
        legend_opts=opts.LegendOpts(is_show=False),
    )
)

# 创建UV折线图
line_uv_hour_1212 = (
    Line(init_opts=opts.InitOpts(width="100vw"))
    .add_xaxis(hour)
    .add_yaxis("UV", uv_hour_1212, is_smooth=True, label_opts=opts.LabelOpts(is_show=True, rotate=45),
               markline_opts=opts.MarkLineOpts(
                   data=[
                       opts.MarkLineItem(type_="average", name="平均值", linestyle_opts=opts.LineStyleOpts(
                           type_="dashed"
                       )),
                       opts.MarkLineItem(type_="min", name="最小值", linestyle_opts=opts.LineStyleOpts(
                           color="#FCC524",
                           type_="dashed"
                       )),
                       opts.MarkLineItem(type_="max", name="最大值", linestyle_opts=opts.LineStyleOpts(
                           color="#E63E31",
                           type_="dashed"
                       )),
                   ],
               )
               )
    .set_global_opts(
        title_opts=opts.TitleOpts(title="uv_hour"),
        xaxis_opts=opts.AxisOpts(type_="category", name="hour"),
        yaxis_opts=opts.AxisOpts(type_="value", name="uv_count", name_location="middle", name_gap=55),
        legend_opts=opts.LegendOpts(is_show=False),
    )

)

# 渲染折线图
line_pv_hour.render("../static/html/pv_hour_1212_line_chart.html")
line_uv_hour.render("../static/html/uv_hour_1212_line_chart.html")
'''
# 插入 pv_hourly_1212 数据
for hour_val, pv in zip(hour, pv_hour_1212):
    sql = """
    INSERT INTO pv_hourly_1212 (hour, pv_count)
    VALUES (%s, %s)
    """
    cursor.execute(sql, (hour_val, pv))
# 插入 uv_hourly 数据
for hour_val, uv in zip(hour, uv_hour_1212):
    sql = """
    INSERT INTO uv_hourly_1212 (hour, uv_count)
    VALUES (%s, %s)
    """
    cursor.execute(sql, (hour_val, uv))

# 提交事务
connection.commit()

print("数据已成功插入到 MySQL 数据库中！")
'''

# 可以看到，双十二当天，PV变化趋与一个月内的PV变化趋势基本一致，只不过曲线不太平滑，多出来小的凸点，
# 一个可能的原因是大促当天，有整点领券活动，通过推送等形式会有效拉动用户使用淘宝。
# UV变化趋势稍有不同，可以看到双十二当天在晚上8点之后UV出现了小高峰，表明了大促当天用户的消费意愿还是比较强烈。


# 1.3 不同用户行为流量分析
# 统计在一天当中（按照每小时）用户发生的行为

# 基于 behavior_type & hour 分组
# 浏览、收藏、加购物车、购买四种行为
behavior = data.groupby(['behavior_type', 'hour'])['user_id'].count()
behavior = behavior.reset_index()
behavior = behavior.rename(columns={'user_id': 'behavior_count'})
print("behavior: \n", behavior.head(10))

import seaborn as sns

# 简单可视化
sns.lineplot(x='hour', y='behavior_count', hue='behavior_type', data=behavior)
plt.savefig("../static/images/behavior_line_chart.png")
plt.show()

# 因为action_type为1（浏览行为）的占比非常大，导致上图其它几类action的趋势不太明显，去掉action_type为1的数据后再来看看
sns.lineplot(x='hour', y='behavior_count', hue='behavior_type', data=behavior[behavior.behavior_type != 'pv'])
plt.savefig("../static/images/behavior_line_chart_without_pv.png")
plt.show()

# 进一步基于双十二当天的数据来分析下4种行为的变化趋
data_1212 = data.loc[data['date'] == '2014-12-12']
# 基于 behavior_type & hour 分组
behavior_1212 = data_1212.groupby(['behavior_type', 'hour'])['user_id'].count()
behavior_1212 = behavior_1212.reset_index()
behavior_1212 = behavior_1212.rename(columns={'user_id': 'behavior_count_1212'})
print("behavior_1212: \n", behavior_1212.head(10))

# 简单可视化
sns.lineplot(x='hour', y='behavior_count_1212', hue='behavior_type', data=behavior_1212)
plt.savefig("../static/images/behavior_line_chart_1212.png")
plt.show()

# 因为action_type为1（浏览行为）的占比非常大，导致上图其它几类action的趋势不太明显，去掉action_type为1的数据后再来看看
sns.lineplot(x='hour', y='behavior_count_1212', hue='behavior_type',
             data=behavior_1212[behavior_1212.behavior_type != 'pv'])
plt.savefig("../static/images/behavior_line_chart_1212_without_pv.png")
plt.show()
'''
for index, row in behavior.iterrows():
    sql = """
    INSERT INTO behavior_type_with_hour (hour, behavior_type, behavior_count) 
    VALUES (%s, %s, %s)
    """
    cursor.execute(sql, (row['hour'], row['behavior_type'], row['behavior_count']))
for index, row in behavior_1212.iterrows():
    sql = """
    INSERT INTO behavior_type_with_hour_1212 (hour, behavior_type, behavior_count) 
    VALUES (%s, %s, %s)
    """
    cursor.execute(sql, (row['hour'], row['behavior_type'], row['behavior_count_1212']))
# 提交事务
connection.commit()

print("behavior数据已成功插入到 MySQL 数据库中！")
'''

# 2. 转化率分析
# 分析"浏览-收藏/加购-购买"链路的转化漏斗模型
behavior_type = data.groupby(['behavior_type'])['user_id'].count()
print(behavior_type)

# 用户浏览（pv）的基数是最大的。再分析加购和收藏
pv_count, fav_count, cart_count, buy_count = (
    behavior_type['pv'], behavior_type['fav'], behavior_type['cart'], behavior_type['buy']
)
# 计算转化率
fav_cart_count = fav_count + cart_count
# 计算浏览到加购和收藏的转化率
pv_to_fav_cart_rate = fav_cart_count / pv_count
pv_to_fav_cart_rate_rounded = round(pv_to_fav_cart_rate, 2)

# 计算浏览到购买的转化率
pv_to_buy_rate = buy_count / pv_count
pv_to_buy_rate_rounded = round(pv_to_buy_rate, 2)

# 计算加购和收藏到购买的转化率
fav_cart_to_buy_rate = buy_count / fav_cart_count
fav_cart_to_buy_rate_rounded = round(fav_cart_to_buy_rate, 2)

# 打印转化率结果
print('浏览->加购和收藏转化率：', pv_to_fav_cart_rate_rounded)
print('浏览->购买转化率：', pv_to_buy_rate_rounded)
print('加购和收藏->购买转化率：', fav_cart_to_buy_rate_rounded)

from pyecharts import options as opts
from pyecharts.charts import Funnel

stages = ["浏览", "加购和收藏", "购买"]
conversion_count = [int(pv_count), int(fav_cart_count), int(buy_count)]
# 计算总和
conversion_total = sum(conversion_count)
# 计算每个阶段的百分比
conversion_percentage = [round((count / conversion_total) * 100, 2) for count in conversion_count]

data_pair = list(zip(stages, conversion_percentage))

# 创建漏斗图
funnel = (
    Funnel()
    .add(
        "转化漏斗",
        data_pair,
        label_opts=opts.LabelOpts(formatter="{b}: {c}%")  # 显示百分比
    )
    .set_global_opts(
        title_opts=opts.TitleOpts(title="转化漏斗模型"),
    )
)
funnel.render("../static/html/conversion_funnel.html")

# 再来看看双十二当天的数据
data_1212 = data.loc[data['date'] == '2014-12-12']
behavior_type_1212 = data_1212.groupby(['behavior_type'])['user_id'].count()
pv_count_1212, fav_count_1212, cart_count_1212, buy_count_1212 = (
    behavior_type_1212['pv'], behavior_type_1212['fav'], behavior_type_1212['cart'], behavior_type_1212['buy']
)
fav_cart_count_1212 = fav_count_1212 + cart_count_1212

print('双十二当天：')
# 计算双十二当天浏览到加购和收藏的转化率
fav_cart_rate_1212 = fav_cart_count_1212 / pv_count_1212
fav_cart_rate_1212_rounded = round(fav_cart_rate_1212, 2)

# 计算双十二当天浏览到购买的转化率
buy_rate_1212 = buy_count_1212 / pv_count_1212
buy_rate_1212_rounded = round(buy_rate_1212, 2)

# 计算双十二当天加购和收藏到购买的转化率
fav_cart_to_buy_rate_1212 = buy_count_1212 / fav_cart_count_1212
fav_cart_to_buy_rate_1212_rounded = round(fav_cart_to_buy_rate_1212, 2)

# 打印双十二当天的转化率结果
print('浏览->加购和收藏转化率：', fav_cart_rate_1212_rounded)
print('浏览->购买转化率：', buy_rate_1212_rounded)
print('加购和收藏->购买转化率：', fav_cart_to_buy_rate_1212_rounded)

from pyecharts import options as opts
from pyecharts.charts import Funnel

stages_1212 = ["浏览", "加购和收藏", "购买"]
conversion_count_1212 = [int(pv_count_1212), int(fav_cart_count_1212), int(buy_count_1212)]
# 处理双十二当天的数据
# 计算总和
conversion_total_1212 = sum(conversion_count_1212)
# 计算每个阶段的百分比
conversion_percentage_1212 = [round((count / conversion_total_1212) * 100, 2) for count in conversion_count_1212]

data_pair_1212 = list(zip(stages_1212, conversion_percentage_1212))

funnel_1212 = (
    Funnel()
    .add(
        "转化漏斗",
        data_pair_1212,
        label_opts=opts.LabelOpts(formatter="{b}: {c}%")  # 显示百分比
    )
    .set_global_opts(
        title_opts=opts.TitleOpts(title="转化漏斗模型"),
    )
)
funnel_1212.render("../static/html/conversion_funnel_1212.html")
'''
for stages_val, percentage_val in zip(stages, conversion_percentage):
    sql = """
    INSERT INTO conversion_percentage (stages, percentage) 
    VALUES (%s, %s)
    """
    cursor.execute(sql, (stages_val, percentage_val))
for stages_val, percentage_val in zip(stages_1212, conversion_percentage_1212):
    sql = """
    INSERT INTO conversion_percentage_1212 (stages, percentage) 
    VALUES (%s, %s)
    """
    cursor.execute(sql, (stages_val, percentage_val))
# 提交事务
connection.commit()

print("转化率数据已成功插入到 MySQL 数据库中！")
'''
# 3. 用户价值分析
# 在分析完整体变化趋势和转化率之后，商家更关注的是用户行为，什么用户是有价值的，什么用户是潜在用户？
# 商业上已经有不少成熟的模型可供参考，如用户价值RFM分析模型等。
# 本项目中我们还是从实际问题出发，占在用户购买行为的角度来探索用户价值：

# 3.1 用户购买频次分析
# 从上面的统计结果来看，用户的点击数据是最多的，但是从淘宝盈利的角度来说，只有购买行为是商家最为关注的
# 这里就会引入一个问题：在给定的数据集上，淘宝用户购买东西的频次有多少？基于这个问题，我们可以用统计的方法计算。
# 浏览 >> 加购/收藏 >> 购买
# select user_id, count(behavior_type) as buy_count where behavior_type = 'buy'
data_user_buy = data[data.behavior_type == 'buy'].groupby('user_id')['behavior_type'].count()
data_user_buy = data_user_buy.reset_index()
data_user_buy = data_user_buy.rename(columns={'behavior_type': 'buy_count'})
print(data_user_buy.head(10))
print('index', data_user_buy.index)

# 可视化
from pyecharts import options as opts
from pyecharts.charts import Bar

# 创建柱状图
user_id_tolist = data_user_buy['user_id'].astype(str).tolist()
buy_count_tolist = data_user_buy['buy_count'].tolist()

user_buy_count_bar = (
    Bar(init_opts=opts.InitOpts(width="100vw"))
    .add_xaxis(user_id_tolist)  # 使用用户 ID 作为 X 轴
    .add_yaxis(
        "购买次数",
        buy_count_tolist,
        itemstyle_opts=opts.ItemStyleOpts(color='blue'),  # 修改颜色为蓝色
        label_opts=opts.LabelOpts(is_show=False)  # 隐藏数据标签
    )
    .set_global_opts(
        title_opts=opts.TitleOpts(title="用户购买频次", subtitle="每个用户的购买行为统计"),
        datazoom_opts=[opts.DataZoomOpts()],  # 添加缩放功能
        xaxis_opts=opts.AxisOpts(name="用户 ID"),
        yaxis_opts=opts.AxisOpts(name="购买次数"),
        # toolbox_opts=opts.ToolboxOpts(is_show=True),  # 显示工具栏
    )
)
user_buy_count_bar.render('../static/html/user_buy_count_bar.html')
'''
# 插入数据
for user_id_val, buy_count_val in zip(user_id_tolist, buy_count_tolist):
    sql = """
    INSERT INTO user_buy_count (user_id, buy_count) 
    VALUES (%s, %s)
    """
    cursor.execute(sql, (user_id_val, buy_count_val))
# 提交事务
connection.commit()

print("用户购买频次数据已成功插入到 MySQL 数据库中！")
'''
# X轴代表user_id，Y轴是每个用户的购买次数。
# 可以看到，大部分用户的购买次数均不超过50次，这期间还包括了双十二当天的集中购物，排除双十二高峰，实际消费次数会更少。
# 当然也有部分用户的购买次数超过百次，甚至有超过800次的，高频消费的用户可以看作是忠实的超级用户。


# 3.2 ARPU分析
# ARPU(Average Revenue Per User) 表示每日的收益在所有活跃用户中的转化。详细的描述为，每日的所有收益与每日的活跃的用户数量有关，

# 因此可以通过ARPU来计算，每日活跃用户对每日收益的转化效率。
# 该数据集中没有对金额的体现。那我们可以对ARPU这个指标做一些修改，改为度量平台活跃用户每日平均消费次数。
# 计算公式为： ARPU = 每日消费总次数 / 每日活跃用户数

# 给数据集中每个用户赋值一个 1，表示有登录操作
data['action'] = 1

# 按照日期、用户 ID 和行为类型分组，计算每日每用户的行为次数
user_behavior_daily = data.groupby(['date', 'user_id', 'behavior_type'])['action'].count().reset_index()

# 筛选出行为类型为 'buy' 的数据，用于计算每日消费次数
daily_buy = user_behavior_daily[user_behavior_daily['behavior_type'] == 'buy']

# 计算每日的消费总次数 和 每日的活跃用户数（所有行为类型的用户数）
daily_total_buy = daily_buy.groupby('date')['action'].sum()
daily_active_users = user_behavior_daily.groupby('date')['user_id'].nunique()

# 计算每日的 ARPU（每日消费总次数 / 每日活跃用户数）
daily_arpu = daily_total_buy / daily_active_users
print(daily_arpu.head(10))

# 可视化每日ARPU变化趋势

# 设置图像大小
plt.figure(figsize=(12, 9))

# 绘制每日ARPU变化趋势图
# 使用 cividis 色彩映射，并添加线条宽度、标记样式和透明度设置
daily_arpu.plot(colormap='cividis', linewidth=2, marker='o', markersize=6, alpha=0.8)
plt.title('每日ARPU变化趋势', fontsize=16, fontweight='bold', color='darkblue', pad=20)
plt.xlabel('日期', fontsize=14, fontweight='bold', color='darkblue')
plt.ylabel('ARPU', fontsize=14, fontweight='bold', color='darkblue')
# 添加网格线
plt.grid(True, linestyle='--', alpha=0.5)
# 自动旋转日期标签，避免重叠
plt.gcf().autofmt_xdate()
# 设置X轴刻度显示格式（例如：只显示部分日期）
plt.gca().xaxis.set_major_locator(plt.MaxNLocator(10))  # 最多显示10个刻度
# 添加图例
plt.legend(['ARPU'], fontsize=12, loc='upper right', framealpha=0.8)
# 调整布局，确保标签和标题不被遮挡
plt.tight_layout()
plt.show()

# 可以看到，活跃用户每天平均消费次数在0.5次左右，双十二期间达到最高值接近2，是平时的4倍左右，表明用户会集中在大促日的时候购买


# 计算ARPU过程中，分子使用的是购买累计次数，如果分子只统计购买的用户数，那么就能得到下单率
# 给行为数据赋值 1，表示用户发生了登录
data['action'] = 1
# 计算每日的活跃用户数（不区分行为类型）
daily_active_users = data.groupby('date')['user_id'].nunique()
# 计算每日购买用户数（只统计行为类型为 'buy' 的用户）
daily_buy_users = data[data['behavior_type'] == 'buy'].groupby('date')['user_id'].nunique()
# 计算每日下单率
daily_order_rate = daily_buy_users / daily_active_users
print("每日下单率：", daily_order_rate)

# 双十二当天的数据对比
print("\n双十二当天下单率：", daily_order_rate.loc['2014-12-12'])

# 可视化每日下单率的变化
plt.figure(figsize=(12, 9))
daily_order_rate.plot(colormap='cividis')
plt.title('ARPU')
plt.ylabel('order rate')
plt.grid(True, linestyle='--', alpha=0.5)
plt.show()

# 可以看到，只统计购买的用户数，下单率平均为0.22左右，而双十二的下单率高达0.5以上。
# 说明了促销活动的强大影响力，同时也暴露了平时用户购买转化率较低的问题。平台可以此为参考，
# 非活动期间针对未转化用户（浏览但未购买的用户）做更多的促销或激励措施，提升平时的下单率。
'''
for date_val, arpu_val in daily_arpu.items():
    sql = """
    INSERT INTO arpu_daily (date, arpu_count)
    VALUES (%s, %s)
    """
    cursor.execute(sql, (date_val, arpu_val))

for date_val, order_rate_val in daily_order_rate.items():
    sql = """
    INSERT INTO order_rate_daily (date, order_rate)
    VALUES (%s, %s)
    """
    cursor.execute(sql, (date_val, order_rate_val))
# 提交事务
connection.commit()

print("ARPU和下单率数据已成功插入到 MySQL 数据库中！")
'''

# 3.3 ARPPU分析
# ARPPU(average revenue per paying user)是指从每位付费用户中获得的收益，它反映的是整个平台的用户消费的均值。
# 它的计算方式为：ARPPU = 总收入/活跃用户付费数量。但是在该数据集中没有收益金额，因此我们可以对计算方式做一点转化，将总收入转化为总的购买行为次数。
# 定义如下：
# ARPPU = 当日总消费次数/当日活跃用户付费数量，可以看出和ARPU唯一的区别是分母，
# ARPU的分母是活跃用户数（包含4种行为类型），ARPPU的分母是活跃付费用户数，因此ARPPU的计算会更简单

# 计算每日每个用户的购买次数
daily_user_purchases = (
    data[data['behavior_type'] == 'buy']
    .groupby(['date', 'user_id'])
    .size()
    .reset_index(name='buy_count')
)
print("每日每位用户的购买次数:")
print(daily_user_purchases.head(10))

# 计算每日ARPPU：总购买次数 / 活跃付费用户数量
arppu_daily = daily_user_purchases.groupby('date').apply(
    lambda x: x['buy_count'].sum() / x['user_id'].nunique()
)
print("每日ARPPU值:")
print(arppu_daily.head(10))

# 可视化每日ARPPU变化趋势
# 设置图像大小
plt.figure(figsize=(12, 9))
# 绘制ARPPU变化趋势图
arppu_daily.plot(colormap='cividis', linewidth=2, marker='o', markersize=6, linestyle='-')
plt.xlabel('日期', fontsize=14, fontweight='bold', color='black')
plt.ylabel('ARPPU', fontsize=14, fontweight='bold', color='black')
plt.title('每日ARPPU变化趋势', fontsize=16, fontweight='bold', color='black', pad=20)
# 添加网格线
plt.grid(True, linestyle='--', alpha=0.5, which='both', axis='both')
# 设置日期格式
plt.gcf().autofmt_xdate()  # 自动旋转日期标签
plt.gca().xaxis.set_major_locator(plt.MaxNLocator(10))  # 设置X轴最多显示10个刻度
# 添加图例
plt.legend(['ARPPU'], fontsize=12, loc='upper right', framealpha=0.8)
plt.tight_layout()
plt.show()

# 可以看到，针对活跃的下单用户来讲，平均每日消费次数在2-2.5次之间波动，双十二当天该数值达超过3.75，
# 一个可能的原因是用户会在平时把喜欢的商品进行加购，等到双十二促销当天再下单购买。
'''
for date_val, arppu_val in arppu_daily.items():
    sql = """
    INSERT INTO arppu_daily (date, arppu_count)
    VALUES (%s, %s)
    """
    cursor.execute(sql, (date_val, arppu_val))
# 提交事务
connection.commit()

print("ARPPU数据已成功插入到 MySQL 数据库中！")
'''

# 3.4 复购情况分析
# 一般来说，复购是指对产品的重复购买行为。但是这个定义在商业上是不精确的，
# 假若一个用户在一天内多次在淘宝购买商品，不能说明这件用户对淘宝的依赖（有可能是某位用户只是第一次用，但是买的量大）。
# 因此商业分析过程中，对于复购行为进行明确的定义。这里的复购是指：两天以上都在该平台产生了购买行为，需要指出一天多次的购买不算是复购。

# 因此复购率的计算方式为：复购率 = 复购用户数量 / 有购买行为的用户数量。基于这个公式，我们先计算用户在数据集中的购买频次：
# 计算用户购买频次
data_user_pay = data[data.behavior_type == 'buy']
# 基于date去重，得到的结果即为购物分布的天数：
data_user_pay = data_user_pay.groupby('user_id')['date'].nunique()
print(data_user_pay.head(10))

# 计算复购率：
# 筛选出购买次数大于1的用户数量
repeat_buy_count = data_user_pay[data_user_pay > 1].count()
# 计算总购买用户数量
total_buy_count = data_user_pay.count()
# 计算复购率
repeat_buy_ratio = repeat_buy_count / total_buy_count
# 打印复购率结果
print("复购率：", repeat_buy_ratio)

# 3.5 复购周期分析
# 除了以上对复购频次的统计，还需要对复购意向做进一步的探究，想要知道用户多久复购一次。
# 这个数据有助于淘宝产品宣传在这个时间间隔内采取策略，增加用户的复购意向，最终转化为实际收益。
data['action'] = 1  # 对每一行的行为记为1次，通过对行为次数的相加，从而计算频次
data_user_buy = data[data.behavior_type == 'buy']
data_user_buy = data_user_buy.groupby(['user_id', 'date'])['action'].count()
data_user_buy = data_user_buy.reset_index()
print(data_user_buy.head(10))

# 对日期排序（按照先后顺序），给予pandas的date函数计算前后两次购物相差的天数，dropna去掉了每个用户在数据集周期内第一次购买日期的记录：
# 确保 'date' 列转换为 datetime 类型
data_user_buy['date'] = pd.to_datetime(data_user_buy['date'])
data_user_buy_date_diff = data_user_buy.groupby('user_id')['date'].apply(lambda x: x.sort_values().diff(1).dropna())
print(data_user_buy_date_diff.head(10))

# 对复购间隔进行可视化结果展示
# 将 Timedelta 转换为天数（整数）
data_user_buy_date_diff_days = data_user_buy_date_diff.dt.days
data_user_buy_date_diff_days_count = data_user_buy_date_diff_days.value_counts().sort_index()
data_user_buy_date_diff_days_count.plot(kind='line')
plt.xlabel('repeat_day_diff (days)')
plt.ylabel('count')
plt.show()

from pyecharts.charts import Line
from pyecharts import options as opts

categories = list(range(1, 31))
# data1 = [int(i) for i in uv_hour['uv_hour']] 舍弃
repeat_purchase_pv = [int(i) for i in data_user_buy_date_diff.value_counts().values]

# 创建折线图
line = (
    Line(init_opts=opts.InitOpts(width="100vw"))
    .add_xaxis(categories)
    .add_yaxis(
        series_name='复购PV',
        y_axis=repeat_purchase_pv
    )
    .set_global_opts(
        title_opts=opts.TitleOpts(title="用户复购行为分析", subtitle="基于PV展示用户的复购周期"),
        xaxis_opts=opts.AxisOpts(name="购买后天数"),  # X轴名称
        yaxis_opts=opts.AxisOpts(name="页面访问量(PV)"),  # Y轴名称
        tooltip_opts=opts.TooltipOpts(trigger="axis", axis_pointer_type="cross")  # 工具提示
    )
    .set_series_opts(
        markarea_opts=opts.MarkAreaOpts(
            data=[
                opts.MarkAreaItem(x=(4, 5), name="典型复购周期开始"),
                opts.MarkAreaItem(x=(14, 15), name="另一个重要复购周期")
            ],
            itemstyle_opts=opts.ItemStyleOpts(color="#3F97FF", opacity=0.4)  # 标记区域颜色
        ),
    )
)

# 渲染图表
line.render("../static/html/repeat_purchase_chart.html")

# 可以看出，大部分用户的复购行为发生在5天之内，在第5天复购的行为出现了明显的拐点，如果这个时候采取营销策略提升用户的购买意图，增加更多收益。
# 超过15天后，复购的意愿基本已经趋于0，此时可以考虑采取一些召回策略，增加复购的可能性，防止用户的流失。

'''
sql = """
INSERT INTO repeat_buy_ratio (ratio)
VALUES (%s)
"""
cursor.execute(sql, repeat_buy_ratio)

for categories_val, repeat_purchase_pv_val in zip(categories, repeat_purchase_pv):
    sql = """
    INSERT INTO repeat_buy_cycle (category, repeat_buy_cycle) 
    VALUES (%s, %s)
    """
    cursor.execute(sql, (categories_val, repeat_purchase_pv_val))

connection.commit()
cursor.close()
connection.close()
print("复购率数据已成功插入到 MySQL 数据库中！")
print("复购周期数据已成功插入到 MySQL 数据库中！")
'''
# 4.RFM结合K-Means聚类分析
# 详见user_behavior_kmeans.py

